﻿CREATE FUNCTION [ODData].[GetDataFromTrajectory](@SeriesID int)
RETURNS @Observation TABLE 	(		
RowID int IDENTITY (1,1) NOT NULL,LocalDateTime  dateTime, UTCOffsetHours real, ObservationValue real, GeoPosition geography
)
AS BEGIN
INSERT  @Observation (ObservationValue, LocalDateTime, UTCOffsetHours) SELECT  ObservedValue, LocalDatetime, UTCOffsetHours from ODData.TimeSeries where seriesid=@SeriesID order by LocalDateTime ASC;
DECLARE @trajectory geography, @featureid int;
SELECT @featureid = FeatureID from ODCore.SeriesCatalog where SeriesID=@SeriesID;
SELECT @trajectory = Geoposition from ODCore.Feature where Featureid=@featureid
IF (@trajectory.STGeometryType()='LineString')
BEGIN
UPDATE @Observation SET Geoposition=@trajectory.STPointN(RowID);
END
ELSE
BEGIN
UPDATE @Observation SET Geoposition=@trajectory;
END
 RETURN;
 END;
